﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace DB
{
    public class ADOHelper
    {
        private static DBHelpers db = DBFactorys.GetDBHelper();
        /// <summary>
        /// 批量插入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="tableName"></param>
        /// <param name="pkName"></param>
        /// <returns></returns>
        public static bool InsertList<T>(IList<T> list, string tableName, string pkName) where T : class, new()
        {
            string sql = string.Join(" ", list.AsEnumerable().Select(t => GetAddStrs<T>(t, tableName, pkName, null, null, false)));
            return db.ExecteNonQueryText(sql) > 0;
        }
        #region 辅助方法
        /// <summary>
        /// 从实体中获取指定字段及其值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="listUpdateField"></param>
        /// <param name="m"></param>
        /// <returns></returns>
        public static Dictionary<string, object> GetKeyValue<T>(IList<string> listUpdateField, T m) where T : class, new()
        {
            Dictionary<string, object> listKV = new Dictionary<string, object>();
            if (m == null)
                return listKV;
            Type type = m.GetType();
            PropertyInfo[] pros = type.GetProperties(); //获得T的属性数组

            foreach (string str in listUpdateField)
            {
                foreach (PropertyInfo pro in pros)
                {
                    if (str == pro.Name)
                    {
                        listKV.Add(str, pro.GetValue(m, null));
                        continue;
                    }
                }
            }

            return listKV;
        }
        public static DbParameter[] ToArrSqlParam<T>(Dictionary<string, T> list)
        {
            return list.Select(d => DBHelpers.CreateParameter(d.Key, d.Value)).ToArray();
        }
        /// <summary>
        /// 将实体转成参数数组
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="m"></param>
        /// <param name="isUpdate"></param>
        /// <param name="PKName"></param>
        /// <returns></returns>
        public static DbParameter[] SetParmValues<T>(T m, bool isUpdate = true, string PKName = "") where T : class, new()
        {
            if (m == null)
                return null;
            DbParameter[] ps;
            long mCount = 0;
            Type type = m.GetType();
            PropertyInfo[] pros = type.GetProperties(); //获得T的属性数组
            pros = pros.Where(s => s.PropertyType.IsValueType||s.PropertyType==typeof(string)).ToArray();
            if (!isUpdate)
            {
                pros = pros.Where(s => s.Name != PKName).ToArray();
            }
            mCount = pros.Count();
            ps = new DbParameter[mCount];
            int i = 0;
            foreach (PropertyInfo pro in pros)
            {
                object val = pro.GetValue(m, null);
                if (pro.PropertyType == typeof(DateTime))
                {
                    if (val != null && (DateTime)val < (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue)
                    {
                        val = System.Data.SqlTypes.SqlDateTime.MinValue;
                    }
                }
                ps[i] = DBHelpers.CreateParameter(pro.Name, val);
                i++;
            }
            return ps;
        }
        /// <summary>
        /// 获取新增记录sql语句(参数化,返回新增id)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="m">实体</param>
        /// <param name="tableName">表名</param>
        /// <param name="PKName">主键</param>
        /// <param name="func"></param>
        /// <param name="removeField">不新增的字段集合</param>
        /// <returns></returns>
        public static string GetAddStr<T>(T m, string tableName, string PKName, Func<PropertyInfo, bool> func = null, IList<string> removeField = null, bool isSelectId = true) where T : class, new()
        {
            Type type = m.GetType();
            PropertyInfo[] pros = type.GetProperties(); //获得T的属性数组
            string sql = string.Empty;
            if (removeField == null || removeField.Count == 0)
            {
                pros = pros.Where(p => p.Name != PKName && (func == null || func.Invoke(p))).ToArray();
            }
            else
            {
                pros = pros.Where(p => p.Name != PKName && (func == null || func.Invoke(p)) && !removeField.Contains(p.Name)).ToArray();
            }
            sql = string.Format("INSERT INTO [{0}]({1}) VALUES({2}) {3} ", tableName,
                string.Join(",", pros.Select(p => string.Format("[{0}]", p.Name))),
                string.Join(",", pros.Select(p => string.Format("@{0}", p.Name))), isSelectId ? " SELECT SCOPE_IDENTITY() " : "");

            return sql;
        }
        /// <summary>
        /// 获取新增记录sql语句(非参数化)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="m"></param>
        /// <param name="tableName"></param>
        /// <param name="PKName"></param>
        /// <param name="func"></param>
        /// <param name="removeField"></param>
        /// <returns></returns>
        public static string GetAddStrs<T>(T m, string tableName, string PKName, Func<PropertyInfo, bool> func = null, IList<string> removeField = null, bool isSelectId = true) where T : class, new()
        {
            Type type = m.GetType();
            PropertyInfo[] pros = type.GetProperties(); //获得T的属性数组
            string sql = string.Empty;
            if (removeField == null || removeField.Count == 0)
            {
                pros = pros.Where(p => p.Name != PKName && (func == null || func.Invoke(p))).ToArray();
            }
            else
            {
                pros = pros.Where(p => p.Name != PKName && (func == null || func.Invoke(p)) && !removeField.Contains(p.Name)).ToArray();
            }
            sql = string.Format("INSERT INTO [{0}]({1}) VALUES({2}) {3}", tableName,
                string.Join(",", pros.Select(p => string.Format("[{0}]", p.Name))),
                string.Join(",", pros.Select(p =>
                {
                    object obj = p.GetValue(m);
                    if (obj != null)
                        return string.Format("'{0}'", obj.ToString().Replace("'", ""));
                    else
                        return string.Format("NULL");
                })), isSelectId ? " SELECT SCOPE_IDENTITY() " : "");

            return sql;
        }
        /// <summary>
        /// 获取根据主键Id更新记录的sql语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="m">实体</param>
        /// <param name="tableName">表名</param>
        /// <param name="PKName">主键</param>
        /// <param name="func">筛选更新字段的Func</param>
        /// <param name="updateField">更新字段数组集合</param>
        /// <returns></returns>
        public static string GetUpdateStr<T>(T m, string tableName, string PKName, Func<PropertyInfo, bool> func = null, params string[] updateField) where T : class, new()
        {
            Type type = m.GetType();
            PropertyInfo[] pros = type.GetProperties(); //获得T的属性数组
            string sql = string.Empty;
            if (updateField == null || updateField.Length == 0)
            {
                pros = pros.Where(p => p.Name != PKName && func.Invoke(p)).ToArray();
            }
            else
            {
                pros = pros.Where(p => p.Name != PKName && func.Invoke(p)).Where(s => updateField.Contains(s.Name)).ToArray();
            }
            sql = string.Format("UPDATE [{0}] SET {1} WHERE [{2}]=@{2}", tableName, string.Join(",", pros.Select(s => string.Format("[{0}]=@{0}", s.Name))), PKName);
            return sql;
        }
        /// <summary>
        /// 获取根据查询条件更新记录的sql语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="m">实体</param>
        /// <param name="tableName">表名</param>
        /// <param name="PKName">主键</param>
        /// <param name="strWhere"></param>
        /// <param name="func">筛选更新字段的Func</param>
        /// <param name="updateField">更新字段数组集合</param>
        /// <returns></returns>
        public static string GetUpdateStr<T>(T m, string tableName, string PKName, string strWhere, Func<PropertyInfo, bool> func = null, params string[] updateField) where T : class, new()
        {
            Type type = m.GetType();
            PropertyInfo[] pros = type.GetProperties(); //获得T的属性数组
            string sql = string.Empty;
            if (string.IsNullOrWhiteSpace(strWhere))
            {
                throw new Exception("条件不能为空");
            }
            else
            {
                if (strWhere.Trim().Substring(0, 3).ToLower() != "and")
                    strWhere = " AND " + strWhere;
            }

            if (updateField == null || updateField.Length == 0)
            {
                pros = pros.Where(p => p.Name != PKName && func.Invoke(p)).ToArray();
            }
            else
            {
                pros = pros.Where(p => p.Name != PKName && func.Invoke(p)).Where(s => updateField.Contains(s.Name)).ToArray();
            }
            sql = string.Format("UPDATE [{0}] SET {1} WHERE 1=1 {2}", tableName, string.Join(",", pros.Select(s => string.Format("[{0}]=@{0}", s.Name))), strWhere);
            return sql;
        }
        #endregion
    }
}
